﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Model;

namespace DAL
{
    public class UserDAL
    {
        DBHelper db = new DBHelper();
        /// <summary>
        /// 查询方法（类型查询）
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Auction> GetListByID(int name)
        {
            string sql = $"select AuctionID, AuctionName, AuctionPicture, AuctionIntroduce, AuctionPrice, AuctionState, AuctionCode,AuctionCategory, AuctionUpload, AuctionAuction from [dbo].[Auction] where AuctionCode='{name}'";
            DataTable dt = db.GetData(sql);
            List<Auction> list = new List<Auction>();
            if (dt.Rows.Count > 0)
            {
                Auction result = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    result = new Auction();
                    result.AuctionID = Convert.ToInt32(dt.Rows[i]["AuctionID"].ToString());
                    result.AuctionName = dt.Rows[i]["AuctionName"].ToString();
                    result.AuctionPicture = dt.Rows[i]["AuctionPicture"].ToString();
                    result.AuctionIntroduce = dt.Rows[i]["AuctionIntroduce"].ToString();
                    result.AuctionPrice = Convert.ToDecimal(dt.Rows[i]["AuctionPrice"].ToString());
                    result.AuctionState = dt.Rows[i]["AuctionState"].ToString();
                    result.AuctionCode = Convert.ToInt32(dt.Rows[i]["AuctionCode"].ToString());
                    result.AuctionCategory = Convert.ToInt32(dt.Rows[i]["AuctionCategory"].ToString());
                    result.AuctionUpload = Convert.ToDateTime(dt.Rows[i]["AuctionUpload"].ToString());
                    result.AuctionAuction = Convert.ToDateTime(dt.Rows[i]["AuctionAuction"].ToString());
                    list.Add(result);
                }
            }
            return list;

        }
        public List<UserInfo> UserInformation()
        {
            try
            {
                string strSql = "select UserID, UserName, UserGender, UserCard, UserTelephone, UserBalance, UserMailbox, UserRegistration from [dbo].[User]";
                DataTable dt = db.GetData(strSql);
                List<UserInfo> list = new List<UserInfo>();
                if (dt.Rows.Count > 0)
                {
                    UserInfo result = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        result = new UserInfo();
                        result.UserID = Convert.ToInt32(dt.Rows[i]["UserID"].ToString());
                        result.UserName = dt.Rows[i]["UserName"].ToString();
                        result.UserGender = dt.Rows[i]["UserGender"].ToString();
                        result.UserCard = dt.Rows[i]["UserCard"].ToString();
                        result.UserTelephone = dt.Rows[i]["UserTelephone"].ToString();
                        result.UserBalance = Convert.ToInt32(dt.Rows[i]["UserBalance"].ToString());
                        result.UserMailbox = dt.Rows[i]["UserMailbox"].ToString();
                        result.UserRegistration = Convert.ToDateTime(dt.Rows[i]["UserRegistration"].ToString());
                        list.Add(result);
                    }
                }
                return list;
            }
            catch (Exception)
            {

                return null;
            }
        }
        public bool UserInsert(UserInfo info)
        {
            string sql = $"insert into [dbo].[User](UserName, UserGender, UserCard, UserTelephone, UserBalance, UserMailbox,UserCode，UserState)values('{info.UserName}','{info.UserGender}','{info.UserCard}','{info.UserTelephone}','{info.UserBalance}','{info.UserMailbox}','{info.UserCode}','{info.UserState}')";
            return db.SetData(sql);
        }
        public bool ADD(SignIn ka)
        {
            string sql = $"insert into [dbo].[SignIn](SignInName, SignInPwd) values('{ka.SignInName}','{ka.SignInPwd}')";
            return db.SetData(sql);
        }
        public List<SignIn> GetListByID()
        {
            string sql = $"select SignInID, SignInName, SignInPwd, SignInState from [dbo].[SignIn]";
            DataTable dt = db.GetData(sql);
            List<SignIn> list = new List<SignIn>();
            if (dt.Rows.Count > 0)
            {
                SignIn result = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    result = new SignIn();
                    result.SignInID = Convert.ToInt32(dt.Rows[i]["SignInID"].ToString());
                    result.SignInName = dt.Rows[i]["SignInName"].ToString();
                    result.SignInPwd = dt.Rows[i]["SignInPwd"].ToString();
                    result.SignInState = dt.Rows[i]["SignInState"].ToString();
                    list.Add(result);
                }
            }
            return list;
        }
        /// <summary>
        /// 显示方法
        /// </summary>
        /// <returns></returns>
        public List<Auction> GetListALL()
        {
            string sql = "select AuctionID, AuctionName, AuctionPicture, AuctionIntroduce, AuctionPrice, AuctionState, AuctionCode,AuctionCategory, AuctionUpload, AuctionAuction from [dbo].[Auction]";
            DataTable dt = db.GetData(sql);
            List<Auction> list = new List<Auction>();
            if (dt.Rows.Count > 0)
            {
                Auction result = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    result = new Auction();
                    result.AuctionID = Convert.ToInt32(dt.Rows[i]["AuctionID"].ToString());
                    result.AuctionName = dt.Rows[i]["AuctionName"].ToString();
                    result.AuctionPicture = dt.Rows[i]["AuctionPicture"].ToString();
                    result.AuctionIntroduce = dt.Rows[i]["AuctionIntroduce"].ToString();
                    result.AuctionPrice = Convert.ToDecimal(dt.Rows[i]["AuctionPrice"].ToString());
                    result.AuctionState = dt.Rows[i]["AuctionState"].ToString();
                    result.AuctionCode = Convert.ToInt32(dt.Rows[i]["AuctionCode"].ToString());
                    result.AuctionCategory = Convert.ToInt32(dt.Rows[i]["AuctionCategory"].ToString());
                    result.AuctionUpload = Convert.ToDateTime(dt.Rows[i]["AuctionUpload"].ToString());
                    result.AuctionAuction = Convert.ToDateTime(dt.Rows[i]["AuctionAuction"].ToString());
                    list.Add(result);
                }
            }
            return list;
        }
        public bool Identity(UserInfo info)
        {
            string sql = $"insert into [dbo].[User](UserName, UserGender, UserCard, UserTelephone, UserBalance, UserMailbox,UserCode,UserState) values('{info.UserName}','{info.UserGender}','{info.UserCard}','{info.UserTelephone}','{info.UserBalance}','{info.UserMailbox}','{info.UserCode}','{info.UserState}')";
            return db.SetData(sql);
        }
        public bool GetLogin(SignIn info)
        {
            try
            {
                string sql = $"select SignInID, SignInName, SignInPwd, SignInState from [dbo].[SignIn] where SignInName='{info.SignInName}' and SignInPwd='{info.SignInPwd}' ";
                DataTable dt = db.GetData(sql);
                List<SignIn> list = new List<SignIn>();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {

                throw;
            }

        }
        /// <summary>
        /// 修改登录状态
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Update(SignIn info)
        {
            string sql = $"update [dbo].[SignIn] set SignInState='在线' where SignInID='{info.SignInID}'";
            if (db.SetData(sql))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //查询方法（名称模糊查询）
        public List<Auction> GetListByName(string Name)
        {
            string sql = $"select AuctionID, AuctionName, AuctionPicture, AuctionIntroduce, AuctionPrice, AuctionState, AuctionCode,AuctionCategory, AuctionUpload, AuctionAuction from [dbo].[Auction] where AuctionName like'%{Name}%'";
            DataTable dt = db.GetData(sql);
            List<Auction> list = new List<Auction>();
            if (dt.Rows.Count > 0)
            {
                Auction result = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    result = new Auction();
                    result.AuctionID = Convert.ToInt32(dt.Rows[i]["AuctionID"].ToString());
                    result.AuctionName = dt.Rows[i]["AuctionName"].ToString();
                    result.AuctionPicture = dt.Rows[i]["AuctionPicture"].ToString();
                    result.AuctionIntroduce = dt.Rows[i]["AuctionIntroduce"].ToString();
                    result.AuctionPrice = Convert.ToDecimal(dt.Rows[i]["AuctionPrice"].ToString());
                    result.AuctionState = dt.Rows[i]["AuctionState"].ToString();
                    result.AuctionCode = Convert.ToInt32(dt.Rows[i]["AuctionCode"].ToString());
                    result.AuctionCategory = Convert.ToInt32(dt.Rows[i]["AuctionCategory"].ToString());
                    result.AuctionUpload = Convert.ToDateTime(dt.Rows[i]["AuctionUpload"].ToString());
                    result.AuctionAuction = Convert.ToDateTime(dt.Rows[i]["AuctionAuction"].ToString());
                    list.Add(result);
                }
            }
            return list;
        }
        /// <summary>
        /// 查询方法（价格查询）
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        public List<Auction> GetListByOrder(int PreOne, int PreTwo)
        {
            string sql = $"select AuctionID, AuctionName, AuctionPicture, AuctionIntroduce, AuctionPrice, AuctionState, AuctionCode,AuctionCategory, AuctionUpload, AuctionAuction from [dbo].[Auction] where AuctionPrice between {PreOne} and {PreTwo}";
            DataTable dt = db.GetData(sql);
            List<Auction> list = new List<Auction>();
            if (dt.Rows.Count > 0)
            {
                Auction result = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    result = new Auction();
                    result.AuctionID = Convert.ToInt32(dt.Rows[i]["AuctionID"].ToString());
                    result.AuctionName = dt.Rows[i]["AuctionName"].ToString();
                    result.AuctionPicture = dt.Rows[i]["AuctionPicture"].ToString();
                    result.AuctionIntroduce = dt.Rows[i]["AuctionIntroduce"].ToString();
                    result.AuctionPrice = Convert.ToDecimal(dt.Rows[i]["AuctionPrice"].ToString());
                    result.AuctionState = dt.Rows[i]["AuctionState"].ToString();
                    result.AuctionCode = Convert.ToInt32(dt.Rows[i]["AuctionCode"].ToString());
                    result.AuctionCategory = Convert.ToInt32(dt.Rows[i]["AuctionCategory"].ToString());
                    result.AuctionUpload = Convert.ToDateTime(dt.Rows[i]["AuctionUpload"].ToString());
                    result.AuctionAuction = Convert.ToDateTime(dt.Rows[i]["AuctionAuction"].ToString());
                    list.Add(result);
                }
            }
            return list;
        }
        /// <summary>
        /// 查询方法（地区查询）
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Auction> GetListByAddress(int address)
        {
            string sql = $"select AuctionID, AuctionName, AuctionPicture, AuctionIntroduce, AuctionPrice, AuctionState, AuctionCode,AuctionCategory, AuctionUpload, AuctionAuction from [dbo].[Auction] where AuctionCategory='{address}'";
            DataTable dt = db.GetData(sql);
            List<Auction> list = new List<Auction>();
            if (dt.Rows.Count > 0)
            {
                Auction result = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    result = new Auction();
                    result.AuctionID = Convert.ToInt32(dt.Rows[i]["AuctionID"].ToString());
                    result.AuctionName = dt.Rows[i]["AuctionName"].ToString();
                    result.AuctionPicture = dt.Rows[i]["AuctionPicture"].ToString();
                    result.AuctionIntroduce = dt.Rows[i]["AuctionIntroduce"].ToString();
                    result.AuctionPrice = Convert.ToDecimal(dt.Rows[i]["AuctionPrice"].ToString());
                    result.AuctionState = dt.Rows[i]["AuctionState"].ToString();
                    result.AuctionCode = Convert.ToInt32(dt.Rows[i]["AuctionCode"].ToString());
                    result.AuctionCategory = Convert.ToInt32(dt.Rows[i]["AuctionCategory"].ToString());
                    result.AuctionUpload = Convert.ToDateTime(dt.Rows[i]["AuctionUpload"].ToString());
                    result.AuctionAuction = Convert.ToDateTime(dt.Rows[i]["AuctionAuction"].ToString());
                    list.Add(result);
                }
            }
            return list;
        }
        /// <summary>
        /// 管理员判断
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool GetGrand(SignIn info)
        {
            try
            {
                string sql = $"select SignInID'ID', SignInName'用户名', SignInPwd'密码', SignInState'状态',UserState '权限' from [dbo].[SignIn] e inner join[dbo].[User] a on e.SignInID = a.UserCode where SignInName = '{info.SignInName}' and SignInPwd = '{info.SignInPwd}' and UserState = '管理员'";
                DataTable dt = db.GetData(sql);
                List<UserInfo> list = new List<UserInfo>();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public bool GetGrandTwo(SignIn info)
        {
            try
            {
                string sql = $"select SignInID'ID', SignInName'用户名', SignInPwd'密码', SignInState'状态',UserState '权限' from [dbo].[SignIn] e inner join[dbo].[User] a on e.SignInID = a.UserCode where SignInName = '{info.SignInName}' and SignInPwd = '{info.SignInPwd}' and UserState = '普通用户'";
                DataTable dt = db.GetData(sql);
                List<UserInfo> list = new List<UserInfo>();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public bool GetCheckUser(SignIn info)
        {
            try
            {
                string sql = $"select SignInID'ID', SignInName'用户名', SignInPwd'密码', SignInState'状态',UserState '权限' from [dbo].[SignIn] e inner join[dbo].[User] a on e.SignInID = a.UserCode where SignInName = '{info.SignInName}'";
                DataTable dt = db.GetData(sql);
                List<UserInfo> list = new List<UserInfo>();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {

                throw;
            }

        }
    }
}
